﻿using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data.SqlTypes;

/// <summary>
/// Interacts with General Forum's database.
/// </summary>
public class ForumDatabaseAdapter
{
    public const string FORUM_DATABASE = "ForumDatabase";
    public const string FORUM_ENTRY_TABLE = "ForumEntryTable";
    public const string COLUMN_SUBJECT = "Subject";
    public const string COLUMN_COMMENT = "Comment";
    public const string COLUMN_SIGNATURE = "Signature";

    public static string[] FORUM_COLUMNS = new string[] {ForumDatabaseAdapter.COLUMN_SUBJECT + " nvarchar(50)",
                                              ForumDatabaseAdapter.COLUMN_COMMENT + " nvarchar(MAX)",
                                              ForumDatabaseAdapter.COLUMN_SIGNATURE + " nvarchar(50)"};

    private string connectionString = "Server=.\\SQLEXPRESS;Initial Catalog=Social Klee;Integrated security=SSPI;";
    //private string connectionString = "Server=.\\SQLEXPRESS;Integrated security=SSPI;";
    // Initial Catalog=Social Klee
    private SqlConnection connection;

	public ForumDatabaseAdapter()
	{

    }

    public string ConnectionString { get; set; }

    /// <summary>
    /// Creates the General Forum database.
    /// </summary>
    /// <remarks>
    /// currently, not used.
    /// </remarks>
    public void CreateDatabase()
    {
        SqlConnection connection = new SqlConnection("Server=.\\SQLEXPRESS;Integrated security=SSPI;");
        String createDatabase = "CREATE DATABASE " + FORUM_DATABASE + " ON PRIMARY " +
                                "(NAME = " + FORUM_DATABASE + "_data," +
                                "FILENAME = 'C:\\users\\public\\ForumDatabase.mdf', " +
                                "SIZE = 3MB, MAXSIZE = 1024MB, FILEGROWTH = 10%) " +
                                "LOG ON (NAME = MyDatabase_Log, " +
                                "FILENAME = 'C:\\users\\public\\ForumDatabase.ldf', " +
                                "SIZE = 3MB, " +
                                "MAXSIZE = 5MB, " +
                                "FILEGROWTH = 10%)";

        SqlCommand command = new SqlCommand(createDatabase, connection);
        try
        {
            command.ExecuteNonQuery();
        }
        catch (Exception ex)
        {

        }
        finally
        {
            //connection.Close();
        }

    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="databaseName"></param>
    /// <remarks>
    /// currently, not used.
    /// </remarks>
    public void CreateDatabase(string databaseName)
    {
        string createDatabase = "CREATE DATABASE " + databaseName + " ON PRIMARY " +
                                "(NAME = " + databaseName + "_data," +
                                "FILENAME = " + string.Format("'C:\\users\\public\\{0}.mdf'", databaseName) + ", " +
                                "SIZE = 3MB, MAXSIZE = 1024MB, FILEGROWTH = 10%) " +
                                "LOG ON (NAME = MyDatabase_Log, " +
                                "FILENAME = " + string.Format("'C:\\users\\public\\{0}.ldf'", databaseName) + ", " +
                                "SIZE = 3MB, " +
                                "MAXSIZE = 5MB, " +
                                "FILEGROWTH = 10%)";

        using (SqlCommand command = new SqlCommand(createDatabase, connection))
        {
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {

            }
        }

    }

    /// <summary>
    /// Creates a table in general forums database.  Accepts an array of column names as a parameter.
    /// </summary>
    /// <param name="tableName"></param>
    /// <param name="columns"></param>
    public void CreateTable(string tableName, string[] columns)
    {
        string commandText = string.Format("CREATE TABLE {0} ( {1} )", tableName, GetColumns(columns));
        try
        {
            using (SqlCommand command = new SqlCommand(commandText, this.connection))
            {
                command.ExecuteNonQuery();
            }
        }
        catch (Exception ex) { }
    }

    /// <summary>
    /// Returns the database columns as an acceptable string for a CREATE TABLE ... query.
    /// </summary>
    /// <param name="columns"></param>
    /// <returns></returns>
    private string GetColumns(string[] columns)
    {
        string output = "";
        int i = 0;

        for (i=0; i < columns.Length-1; i++)
            output += string.Format("{0}, ",columns[i]);
        output += columns[i];

        return output;
    }

    /// <summary>
    /// Open database
    /// </summary>
    public void Open()
    {
        connection = new SqlConnection(connectionString);
        connection.Open();
    }

    /// <summary>
    /// Close database
    /// </summary>
    public void Close()
    {
        connection.Close();
    }

    /// <summary>
    /// Checks if database exists.
    /// </summary>
    /// <param name="tableName"></param>
    /// <returns>
    /// returns true if table exists in database
    /// </returns>
    /// 
    public bool DoesTableExist(string tableName)
    {
        bool doesExist = false;
        string commandString = string.Format("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{0}'", tableName);
        //string commandString = string.Format("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{0}'", "tableNothing");
        SqlCommand command = new SqlCommand(commandString, connection);

        try {
            Object result = command.ExecuteScalar();

            if (result != null) 
                doesExist = true;
        }
        catch (Exception ex) { }

        return doesExist;
    }

    /// <summary>
    /// Insert forum into forumListTable (table tracks the forum names)
    /// </summary>
    /// <param name="forumName"></param>
    /// <param name="tableName"></param>
    /// <param name="description"></param>
    public void InsertToForumList(string forumName, string tableName, string description)
    {
        string commandText = string.Format("INSERT INTO {0} VALUES ('{1}', '{2}', '{3}')", 
                                            Forum.FORUM_LIST_TABLE, forumName, tableName, description);

        using (SqlCommand command = new SqlCommand(commandText, this.connection)) 
        {
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception ex) { }
        }
    }

    /// <summary>
    /// Inserts a post into the General Forum database.
    /// </summary>
    /// <param name="subject"></param>
    /// <param name="comment"></param>
    /// <param name="signature"></param>
    public void AddPost(string subject, string comment, string signature)
    {
        string commandString = "Insert into " + FORUM_ENTRY_TABLE + " values(@subject, @comment, @signature)";
        using (SqlCommand command = new SqlCommand(commandString, connection))
        {
            command.Parameters.Add(new SqlParameter("@subject", System.Data.SqlDbType.NVarChar, 50));
            command.Parameters["@subject"].Value = subject;

            command.Parameters.Add(new SqlParameter("@comment", System.Data.SqlDbType.NVarChar, 4000));
            command.Parameters["@comment"].Value = comment;

            command.Parameters.Add(new SqlParameter("@signature", System.Data.SqlDbType.NVarChar, 50));
            command.Parameters["@signature"].Value = signature;

            command.ExecuteNonQuery();
        }

    }

    /// <summary>
    /// Add a post to a specific forum (table name)
    /// </summary>
    /// <param name="subject"></param>
    /// <param name="comment"></param>
    /// <param name="signature"></param>
    /// <param name="tableName"></param>
    public void AddPost(string subject, string comment, string signature, string tableName)
    {
        string commandString = "Insert into " + tableName + " values(@subject, @comment, @signature)";
        try
        {
            using (SqlCommand command = new SqlCommand(commandString, connection))
            {
                command.Parameters.Add(new SqlParameter("@subject", System.Data.SqlDbType.NVarChar, 50));
                command.Parameters["@subject"].Value = subject;

                command.Parameters.Add(new SqlParameter("@comment", System.Data.SqlDbType.NVarChar, 4000));
                command.Parameters["@comment"].Value = comment;

                command.Parameters.Add(new SqlParameter("@signature", System.Data.SqlDbType.NVarChar, 50));
                command.Parameters["@signature"].Value = signature;

                command.ExecuteNonQuery();
            }
        }
        catch (Exception ex) { }

    }

    /// <summary>
    /// Return all the posts in the database as a List object.
    /// </summary>
    /// <returns></returns>
    public List<ForumEntry> GetAllPosts()
    {
        List<ForumEntry> forumEntries = new List<ForumEntry>();
        string commandString = string.Format("Select * from {0}", FORUM_ENTRY_TABLE);

        using (SqlCommand command = new SqlCommand(commandString, this.connection))
        {
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                ForumEntry entry = new ForumEntry();
                entry.Subject = reader.GetString(0);
                entry.Comment = reader.GetString(1);
                entry.Signature = reader.GetString(2);
                forumEntries.Add(entry);
            }
                
        }

        return forumEntries;
    }

    /// <summary>
    /// Get all the posts for this forum (given tableName)
    /// </summary>
    /// <param name="tableName"></param>
    /// <returns></returns>
    public List<ForumEntry> GetAllPosts(string tableName)
    {
        List<ForumEntry> forumEntries = new List<ForumEntry>();
        string commandString = string.Format("Select * from {0}", tableName);

        using (SqlCommand command = new SqlCommand(commandString, this.connection))
        {
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                ForumEntry entry = new ForumEntry();
                entry.Subject = reader.GetString(0);
                entry.Comment = reader.GetString(1);
                entry.Signature = reader.GetString(2);
                forumEntries.Add(entry);
            }

        }

        return forumEntries;
    }

    /// <summary>
    /// Get the entries
    /// </summary>
    /// <param name="tableName"></param>
    /// <returns></returns>
    public SqlDataReader GetEntries(string tableName)
    {
        string commandString = string.Format("Select * from {0}", tableName);

        using (SqlCommand command = new SqlCommand(commandString, this.connection))
        {
            return command.ExecuteReader();
        }
    }

}